package cn.hs.ph.dao.impl;

import cn.hs.ph.dao.CourierStationDao;
import cn.hs.ph.entity.CourierStation;
import cn.hs.ph.util.JDBCDruidUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class CourierStationDaoImpl implements CourierStationDao {
    @Override
    public int insert(CourierStation courierStation) {
        int num = 0;
        String sql = "insert into courierstation(courierStationName," +
                "courierStationProvince," +
                "courierStationCity," +
                "courierStationDistrict," +
                "courierStationAddress," +
                "courierStationCommunityId," +
                "addTime,courierStationCommunityName)" +
                " values(?,?,?,?,?,?,?,(SELECT communityName FROM community WHERE communityId=?))";

        Object[] obj = {
                courierStation.getCourierStationName(),
                courierStation.getCourierStationProvince(),
                courierStation.getCourierStationCity(),
                courierStation.getCourierStationDistrict(),
                courierStation.getCourierStationAddress(),
                courierStation.getCourierStationCommunityId(),
                new Date(),
                courierStation.getCourierStationCommunityId()
        };
        num = JDBCDruidUtil.update(sql, obj);
        return num;
    }

    @Override
    public int delete(int courierStationId) {
        return 0;
    }

    @Override//修改
    public int update(CourierStation courierStation) {
        int num = 0;
        String sql = "update courierStation set courierStationName=?," +
                "courierStationProvince=?," +
                "courierStationCity=?," +
                "courierStationDistrict=?," +
                "courierStationAddress=?," +
                "courierStationCommunityId=?," +
                "courierStationCommunityName=(SELECT communityName FROM community WHERE communityId=?)" +
                " where courierStationId=?";
        Object[] obj = {
                courierStation.getCourierStationName(),
                courierStation.getCourierStationProvince(),
                courierStation.getCourierStationCity(),
                courierStation.getCourierStationDistrict(),
                courierStation.getCourierStationAddress(),
                courierStation.getCourierStationCommunityId(),
                courierStation.getCourierStationCommunityId(),
                courierStation.getCourierStationId()
        };
        num = JDBCDruidUtil.update(sql, obj);
        return num;
    }

    @Override
    public List<CourierStation> selectAll(Map<String, String> map) {
        List<CourierStation> list = new ArrayList<>();
//        动态查询
        String sql = "select * from courierstation where 1=1 ";
        List<Object> params = new ArrayList<>();
//        模糊查询 驿站名称
        String courierStationName = map.get("courierStationName");
        if (courierStationName != null && !"".equals(courierStationName)) {
            sql += "and courierStationName like ? ";
            params.add("%" + courierStationName + "%");
        }
//        模糊查询 小区名称
        String courierStationCommunityName = map.get("courierStationCommunityName");
        if (courierStationCommunityName != null && !"".equals(courierStationCommunityName)) {
            sql += "and courierStationCommunityName like ? ";
            params.add("%" + courierStationCommunityName + "%");
        }
//        时间范围
        String startTime = map.get("startTime");
        String endTime = map.get("endTime");
        if (startTime != null && !"".equals(startTime)) {
            sql += "and addTime between ? and ? ";
            params.add(startTime);
            params.add(endTime);
        }
//        分页查询
        String pn = map.get("pageNum");
        if (pn != null && !"".equals(pn)) {
            sql += "limit ?, ? ";
            int pageNum = Integer.parseInt(pn);
            int pageSize = Integer.parseInt(map.get("pageSize"));
            params.add((pageNum - 1) * pageSize);
            params.add(pageSize);
        }
//        最终查询
        Object[] obj = params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true) {
            try {
                if (!res.next()) break;
                CourierStation courierStation = new CourierStation(
                        res.getInt(1),
                        res.getString(2),
                        res.getString(3),
                        res.getString(4),
                        res.getString(5),
                        res.getString(6),
                        res.getString(7),
                        res.getInt(8),
                        res.getDate(9),
                        res.getInt(10),
                        res.getInt(11),
                        res.getString(12),
                        res.getInt(13)
                );
                list.add(courierStation);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCDruidUtil.close();
        return list;
    }

    @Override
    public int selectCount(Map<String, String> map) {
        int count = 0;
//        动态查询
        String sql = "select count(1) from courierstation where 1=1 ";
        List<Object> params = new ArrayList<>();
//        模糊查询 驿站名称
        String courierStationName = map.get("courierStationName");
        if (courierStationName != null && !"".equals(courierStationName)) {
            sql += "and courierStationName like ? ";
            params.add("%" + courierStationName + "%");
        }
//        模糊查询 小区名称
        String courierStationCommunityName = map.get("courierStationCommunityName");
        if (courierStationCommunityName != null && !"".equals(courierStationCommunityName)) {
            sql += "and courierStationCommunityName like ? ";
            params.add("%" + courierStationCommunityName + "%");
        }
//        时间范围
        String startTime = map.get("startTime");
        String endTime = map.get("endTime");
        if (startTime != null && !"".equals(startTime)) {
            sql += "and addTime between ? and ? ";
            params.add(startTime);
            params.add(endTime);
        }
//        最终查询
        Object[] obj = params.toArray();
        ResultSet res = JDBCDruidUtil.query(sql, obj);
        while (true) {
            try {
                if (!res.next()) break;
                count = res.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JDBCDruidUtil.close();
        return count;
    }

    @Override
    public int updateStatus(CourierStation courierStation) {
        int num = 0;
        String sql = "update courierStation " +
                "set courierStationStatus=? where courierStationId=?";
        Object[] obj = {
                courierStation.getCourierStationStatus(),
                courierStation.getCourierStationId()
        };
        num = JDBCDruidUtil.update(sql, obj);
        return num;
    }
}
